/* 
 * Copyright (C) 2010 Christian Schneider
 * 
 * This file is part of NS2 news.
 * 
 * NS2 news is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * 
 * NS2 news is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with NS2 news.  If not, see <http://www.gnu.org/licenses/>.
 * 
 */

package tritop.android.naturalselectionnews;

import java.util.ArrayList;
import java.util.Iterator;

import org.json.JSONException;
import org.json.JSONObject;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteConstraintException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;

public class DBHelper extends SQLiteOpenHelper implements IGLOBALS{

	public static final String DATABASE_NAME="tsa_stats.db";
	private static final int DATABASE_VERSION=1;
	
	
	public static final String WAR_STATS_DATA_DATE="date";
	public static final String WAR_STATS_DATA_MAP="map";
	public static final String WAR_STATS_DATA_LENGTH="length";
	public static final String WAR_STATS_DATA_WINNER="winner";
	public static final String WAR_STATS_DATA_VERSION="version";
	
	public static final String KILL_STATS_DATA_MAP ="map";
	public static final String KILL_STATS_DATA_ATTACKER_TEAM="attacker_team";
	public static final String KILL_STATS_DATA_ATTACKER_Z="attackerz";
	public static final String KILL_STATS_DATA_TARGET_WEAPON="target_weapon"; 
	public static final String KILL_STATS_DATA_TARGET_TYPE="target_type";
	public static final String KILL_STATS_DATA_TARGET_ATTACKER_TYPE="attacker_type";
	public static final String KILL_STATS_DATA_TARGET_VERSION="version";
	public static final String KILL_STATS_DATA_TARGET_Z="targetz";
	public static final String KILL_STATS_DATA_TARGET_X="targetx";
	public static final String KILL_STATS_DATA_TARGET_Y="targety";
	public static final String KILL_STATS_DATA_DATE="date";
	public static final String KILL_STATS_DATA_TARGET_LIFETIME="target_lifetime";
	public static final String KILL_STATS_DATA_TARGET_TEAM="target_team";
	public static final String KILL_STATS_DATA_ATTACKER_Y="attackery";
	public static final String KILL_STATS_DATA_ATTACKER_X="attackerx";
	public static final String KILL_STATS_DATA_ATTACKER_WEAPON="attacker_weapon";
	
	public static final String NEWS_FEED_DATA_TITLE ="title";
	public static final String NEWS_FEED_DATA_LINK ="link";
	public static final String NEWS_FEED_DATA_DATE ="updated";
	
	public static final String TWITTER_FEED_DATA_TITLE ="title";
	public static final String TWITTER_FEED_DATA_GUID ="guid";
	public static final String TWITTER_FEED_DATA_DATE ="pubdate";
	
	
	private static final String WAR_STATS_TABLE_NAME="warstats";
	private static final String CREATE_WAR_STATS_TABLE_STATEMENT="CREATE TABLE IF NOT EXISTS "+
	WAR_STATS_TABLE_NAME+"(id integer PRIMARY KEY AUTOINCREMENT," +
	WAR_STATS_DATA_DATE+" integer," +
	WAR_STATS_DATA_MAP+" varchar(100),"+
	WAR_STATS_DATA_LENGTH+" float,"+
	WAR_STATS_DATA_WINNER+" integer,"+
	WAR_STATS_DATA_VERSION+" float," +
	"CONSTRAINT uc_warstats UNIQUE("+WAR_STATS_DATA_DATE+","+WAR_STATS_DATA_LENGTH+"))";
	
	private static final String KILL_STATS_TABLE_NAME="killstats";
	private static final String CREATE_KILL_STATS_TABLE_STATEMENT="CREATE TABLE IF NOT EXISTS "+
	KILL_STATS_TABLE_NAME+"(id integer PRIMARY KEY AUTOINCREMENT," +
	KILL_STATS_DATA_MAP+" VARCHAR(100),"+
	KILL_STATS_DATA_ATTACKER_TEAM+" INTEGER,"+
	KILL_STATS_DATA_ATTACKER_Z+" FLOAT,"+
	KILL_STATS_DATA_TARGET_WEAPON+" VARCHAR(100),"+
	KILL_STATS_DATA_TARGET_TYPE+" VARCHAR(100),"+
	KILL_STATS_DATA_TARGET_ATTACKER_TYPE+" VARCHAR(100),"+
	KILL_STATS_DATA_TARGET_VERSION+" FLOAT,"+
	KILL_STATS_DATA_TARGET_Z+" FLOAT,"+
	KILL_STATS_DATA_TARGET_X+" FLOAT,"+
	KILL_STATS_DATA_TARGET_Y+" FLOAT,"+
	KILL_STATS_DATA_DATE+" INTEGER,"+
	KILL_STATS_DATA_TARGET_LIFETIME+" FLOAT,"+
	KILL_STATS_DATA_TARGET_TEAM+" INTEGER,"+
	KILL_STATS_DATA_ATTACKER_Y+" FLOAT,"+
	KILL_STATS_DATA_ATTACKER_X+" FLOAT,"+
	KILL_STATS_DATA_ATTACKER_WEAPON+" VARCHAR(100)," +
	"CONSTRAINT uc_killstats UNIQUE("+KILL_STATS_DATA_DATE+","+KILL_STATS_DATA_TARGET_LIFETIME+"))";
	
	private static final String NEWS_FEED_TABLE_NAME="newsfeed";
	private static final String CREATE_NEWS_FEED_TABLE_STATEMENT="CREATE TABLE IF NOT EXISTS "+
	NEWS_FEED_TABLE_NAME+"(id integer PRIMARY KEY AUTOINCREMENT," +
	NEWS_FEED_DATA_TITLE+" varchar(1000),"+
	NEWS_FEED_DATA_LINK+" varchar(500),"+
	NEWS_FEED_DATA_DATE+" integer UNIQUE"+
	")";
	
	private static final String TWITTER_FEED_TABLE_NAME="twitterfeed";
	private static final String CREATE_TWITTER_FEED_TABLE_STATEMENT="CREATE TABLE IF NOT EXISTS "+
	TWITTER_FEED_TABLE_NAME+"(id integer PRIMARY KEY AUTOINCREMENT," +
	TWITTER_FEED_DATA_TITLE+" varchar(200),"+
	TWITTER_FEED_DATA_GUID+" integer,"+
	TWITTER_FEED_DATA_DATE+" integer UNIQUE"+
	")";
	
	private static final String LOGTAG="DBHelper";
	
	private SQLiteDatabase mDB;
	
	private SQLiteStatement mInsertKillStatement;
	private SQLiteStatement mInsertWarStatement;
	private SQLiteStatement mInsertNewsStatement;
	private SQLiteStatement mInsertTwitterStatement;
	
	private SQLiteStatement mTweetMaxIdStm;
	private SQLiteStatement mWarWhoIsWinningStm;
	
	
	private static ArrayList<String> killColumns = new ArrayList<String>();
	static{
		killColumns.add(KILL_STATS_DATA_MAP) ;
		killColumns.add(KILL_STATS_DATA_ATTACKER_TEAM);
		killColumns.add(KILL_STATS_DATA_ATTACKER_Z);
		killColumns.add(KILL_STATS_DATA_TARGET_WEAPON); 
		killColumns.add(KILL_STATS_DATA_TARGET_TYPE);
		killColumns.add(KILL_STATS_DATA_TARGET_ATTACKER_TYPE);
		killColumns.add(KILL_STATS_DATA_TARGET_VERSION);
		killColumns.add(KILL_STATS_DATA_TARGET_Z);
		killColumns.add(KILL_STATS_DATA_TARGET_X);
		killColumns.add(KILL_STATS_DATA_TARGET_Y);
		killColumns.add(KILL_STATS_DATA_DATE);
		killColumns.add(KILL_STATS_DATA_TARGET_LIFETIME);
		killColumns.add(KILL_STATS_DATA_TARGET_TEAM);
		killColumns.add(KILL_STATS_DATA_ATTACKER_Y);
		killColumns.add(KILL_STATS_DATA_ATTACKER_X);
		killColumns.add(KILL_STATS_DATA_ATTACKER_WEAPON);
	}
	
	public DBHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		if(DEBUG_ON){
			Log.e(LOGTAG,"On Constructor ");
		}
		mDB=getWritableDatabase();
		mInsertWarStatement = mDB.compileStatement("INSERT INTO "+WAR_STATS_TABLE_NAME+" ("+WAR_STATS_DATA_DATE+","+WAR_STATS_DATA_MAP+","+WAR_STATS_DATA_LENGTH+","+WAR_STATS_DATA_WINNER+","+WAR_STATS_DATA_VERSION+") VALUES (?,?,?,?,?)");
		mInsertNewsStatement = mDB.compileStatement("INSERT INTO "+NEWS_FEED_TABLE_NAME+" ("+NEWS_FEED_DATA_TITLE+","+NEWS_FEED_DATA_LINK+","+NEWS_FEED_DATA_DATE+") VALUES (?,?,?)");
		mInsertTwitterStatement = mDB.compileStatement("INSERT INTO "+TWITTER_FEED_TABLE_NAME+" ("+TWITTER_FEED_DATA_TITLE+","+TWITTER_FEED_DATA_GUID+","+TWITTER_FEED_DATA_DATE+") VALUES (?,?,?)");
		
		mTweetMaxIdStm= mDB.compileStatement("SELECT MAX("+TWITTER_FEED_DATA_GUID+") FROM "+TWITTER_FEED_TABLE_NAME);
		mWarWhoIsWinningStm=mDB.compileStatement("SELECT COUNT(*) FROM "+WAR_STATS_TABLE_NAME+" WHERE "+WAR_STATS_DATA_WINNER+"=? AND "+WAR_STATS_DATA_VERSION+"=?");
		buildKillStatement();
		
		
		
	}

	//*********************************************************
	// Helper builds INSERT statement for the killstats
	//*********************************************************
	
	private void buildKillStatement() {
		StringBuilder sbFields = new StringBuilder();
		StringBuilder sbQM = new StringBuilder();
		sbFields.append(killColumns.get(0));
		sbQM.append("?");
		for (int i=1; i<killColumns.size(); i++) {
			sbFields.append(",");
			sbQM.append(",");
			sbFields.append(killColumns.get(i));
			sbQM.append("?");
		}
		//Log.e(LOGTAG,sbFields.toString());
		//Log.e(LOGTAG,sbQM.toString());
		mInsertKillStatement = mDB.compileStatement("INSERT INTO "+KILL_STATS_TABLE_NAME+" ("+sbFields.toString()+") VALUES ("+sbQM.toString()+")");
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		if(DEBUG_ON){
			Log.e(LOGTAG,"On Create ");
		}
		db.execSQL(CREATE_WAR_STATS_TABLE_STATEMENT);
		db.execSQL(CREATE_KILL_STATS_TABLE_STATEMENT);
		db.execSQL(CREATE_NEWS_FEED_TABLE_STATEMENT);
		db.execSQL(CREATE_TWITTER_FEED_TABLE_STATEMENT);
	}


	//********************************************************************
	// Upgrade from an older DB version to a newer (delete old create new)
	//********************************************************************
	
	@Override
	public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
		if(DEBUG_ON){
			Log.e(LOGTAG,"On Upgrade ");
		}
		db.execSQL("DROP TABLE IF EXISTS "+WAR_STATS_TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS "+KILL_STATS_TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS "+NEWS_FEED_TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS "+TWITTER_FEED_TABLE_NAME);
		onCreate(db);
	}

	
	//*****************************************************************************
	// Insert values in warstats table this happens inside a beginTransaction block
	//*****************************************************************************
	
	public synchronized void insertBulkWarStatsValues(long date,String map,double length,int winner,double version){
		try{
			if(DEBUG_ON){
				Log.e(LOGTAG,"BULK inserting "+map+" winner"+winner+" date"+date+" length"+length+" version"+version);
			}
			mInsertWarStatement.bindLong(1, date);
			mInsertWarStatement.bindString(2, map);
			mInsertWarStatement.bindDouble(3, length);
			mInsertWarStatement.bindLong(4, winner);
			mInsertWarStatement.bindDouble(5, version);
			mInsertWarStatement.executeInsert();
		}
		catch(SQLiteConstraintException e){
			if(DEBUG_ON){
				Log.e(LOGTAG,"BULK war inserting exception ");
			}
		}
	}
	
	
	//*****************************************************************************
	// Insert values in news table this happens inside a beginTransaction block
	//*****************************************************************************
	
	public synchronized void insertBulkNewsValues(String title,String link,long date){
		try{
			if(DEBUG_ON){
				Log.e(LOGTAG,"BULK inserting "+title+" date"+date+" link"+link);
			}
			mInsertNewsStatement.bindString(1, title);
			mInsertNewsStatement.bindString(2, link);
			mInsertNewsStatement.bindLong(3, date);
			mInsertNewsStatement.executeInsert();
		}
		catch(SQLiteConstraintException e){
			if(DEBUG_ON){
				Log.e(LOGTAG,"BULK news inserting exception ");
			}
		}
	}
	
	//*****************************************************************************
	// Insert values in tweet table this happens inside a beginTransaction block
	//*****************************************************************************
	
	public synchronized void insertBulkTwitterValues(String title,long guid,long date){
		try{
			if(DEBUG_ON){
				Log.e(LOGTAG,"BULK inserting "+title+" date"+date+" link"+guid);
			}
			mInsertTwitterStatement.bindString(1, title);
			mInsertTwitterStatement.bindLong(2, guid);
			mInsertTwitterStatement.bindLong(3, date);
			mInsertTwitterStatement.executeInsert();
		}
		catch(SQLiteConstraintException e){
			if(DEBUG_ON){
				Log.e(LOGTAG,"BULK news inserting exception ");
			}
		}
	}
	
	//*****************************************************************************
	// Insert values in killstats table this happens inside a beginTransaction block
	//*****************************************************************************
	public synchronized void insertBulkKillStatsValues(JSONObject killDataObject){
		try{
			if(DEBUG_ON){
				Log.e(LOGTAG,"bulk inserting kill");
			}
		mInsertKillStatement.clearBindings();
		Iterator<String> keys = killDataObject.keys();
		while(keys.hasNext()){
			String key=keys.next();
			if(key!=null && key.length()>0){
				String value;
				try {
					value = killDataObject.getString(key);
					mInsertKillStatement.bindString(killColumns.indexOf(key)+1, value);
				} catch (JSONException e) {
					e.printStackTrace();
				}
			}
		}
		mInsertKillStatement.executeInsert();
		}catch(SQLiteConstraintException e){
			if(DEBUG_ON){
				Log.e(LOGTAG,"BULK kill Constraint exception ");
			}
		}
	}
	
	
	
	public void beginBulkWork(){
		if(DEBUG_ON){
			Log.e(LOGTAG,"Start BULK inserting ");
		}
		mDB.beginTransaction();
	}
	
	public void endBulkWork(){
		if(DEBUG_ON){
			Log.e(LOGTAG,"End BULK inserting ");
		}
		mDB.setTransactionSuccessful();
		mDB.endTransaction();
	}
	
	
	
	public synchronized void insertWarStatsValues(long date,String map,double length,int winner,double version){
		if(DEBUG_ON){
			Log.e(LOGTAG,"inserting "+map+" winner"+winner);
		}
		ContentValues val=new ContentValues();
		val.put(WAR_STATS_DATA_DATE, date);
		val.put(WAR_STATS_DATA_MAP, map);
		val.put(WAR_STATS_DATA_LENGTH, length);
		val.put(WAR_STATS_DATA_WINNER, winner);
		val.put(WAR_STATS_DATA_VERSION, version);
		insertWarStatsValues(val);
	}
	
	public synchronized void insertWarStatsValues(ContentValues val){
			mDB.insert(WAR_STATS_TABLE_NAME, null, val);
	}
	
	public synchronized void insertKillStatsValues(JSONObject killDataObject){
		if(DEBUG_ON){
			Log.e(LOGTAG,"inserting kill");
		}
		ContentValues val=new ContentValues();
		Iterator<String> keys = killDataObject.keys();
		while(keys.hasNext()){
			String key=keys.next();
			if(key!=null && key.length()>0){
				String value;
				try {
					value = killDataObject.getString(key);
					val.put(key, value);
				} catch (JSONException e) {
					e.printStackTrace();
				}
			}
		}
		
		insertKillStatsValues(val);
	}
	
	
	public synchronized void insertKillStatsValues(ContentValues val){
			mDB.insert(KILL_STATS_TABLE_NAME, null, val);
	}
	
	
	public synchronized void insertNewsFeedValues(ContentValues val){
		mDB.insert(NEWS_FEED_TABLE_NAME, null, val);
	}
	
	
	//*****************************************************************************
	// returns the guid of the latest tweet we saved
	//*****************************************************************************
	
	public synchronized long getMaxTwitterGuid(){
		long guid=1;
		guid=mTweetMaxIdStm.simpleQueryForLong();
		if(DEBUG_ON){
			Log.e(LOGTAG,"getMAxTwitterGuid: "+guid);
		}
		guid=(guid<1)?1:guid;
		return guid;
	}
	
	
	//*****************************************************************************
	// return how often has team x won in build y
	//*****************************************************************************
	
	public synchronized long getTeamWins(int team,int build){
		long wins=0;
		mWarWhoIsWinningStm.bindLong(1, team);
		mWarWhoIsWinningStm.bindLong(2, build);
		wins=mWarWhoIsWinningStm.simpleQueryForLong();
		if(DEBUG_ON){
			Log.e(LOGTAG,"Team :"+team+"Wins: "+wins);
		}
		wins=(wins<0)?0:wins;
		return wins;
	}
	
	
	//*****************************************************************************
	// get the last 5 Tweets
	//*****************************************************************************
	
	public synchronized String[] getTweets(){
		String[] tweets=null;
		if(!mDB.isOpen()){
			mDB = getWritableDatabase();
		}
		Cursor cur=mDB.query(TWITTER_FEED_TABLE_NAME, new String[]{TWITTER_FEED_DATA_TITLE}, null, null, null, null, " "+TWITTER_FEED_DATA_GUID+" DESC ", " 5 ");
		if(cur != null){
			cur.moveToFirst();
			ArrayList list = new ArrayList<String>();
			while (cur.isAfterLast() == false) {
	            list.add(cur.getString(0));
	       	    cur.moveToNext();
	        }
	        cur.close();
	        tweets = (String[]) list.toArray(new String[list.size()]);
		}
		return tweets;
	}
	
	@Override
	public synchronized void close() {
		if(mDB!=null){
			mDB.close();
		}
		super.close();
	}
	
	
	//*****************************************************************************
	// Delete Data older than x days
	//*****************************************************************************
	
	public synchronized void logrotate(int deleteDays){
		long timeNow = System.currentTimeMillis();
		long deleteBefore = deleteDays * MILLIS_PER_DAY;
		deleteOldWarstats((timeNow-deleteBefore));
		deleteOldKillstats((timeNow-deleteBefore));
	}
	
	public synchronized void deleteOldWarstats(long timestamp){
		mDB.delete(WAR_STATS_TABLE_NAME, WAR_STATS_DATA_DATE+"<"+timestamp, null);
	}
	
	public synchronized void deleteOldKillstats(long timestamp){
		mDB.delete(KILL_STATS_TABLE_NAME, KILL_STATS_DATA_DATE+"<"+timestamp, null);
	}
	
}
